package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;

import util.DBTool;
import model.Account;
import model.AccountQuestion;
import model.Message;
import model.Page;
import model.Post;
import model.User;
import model.UserLoginLog;
import DAO.AccountDAO;

public class AccountImple implements AccountDAO {

	public int addAccount(Account account) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		Post post = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select * from T_Post where postId = 1";

		try {
			pstmt = dbt.getPstmt(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				post = new Post();
				post.setPostName(rs.getString("postName"));
				User user = new User();
				user.setId(rs.getInt("postUser"));
				post.setPostUser(user);
				
				account.setMgr(post.getPostUser());
				
				sql = "insert into T_Account(mgrId,useType,moneyQuantity,description,useTime,recorderId,recordTime)" + 
						"values(?,?,?,?,?,?,?)";
				pstmt = dbt.getPstmt(sql);
				pstmt.setInt(1,account.getMgr().getId());
				pstmt.setInt(2,(account.getUseType().equals("0")?0:1));
				pstmt.setDouble(3,account.getMoneyQuantity());
				pstmt.setString(4, account.getDescription());
				pstmt.setString(5, (new SimpleDateFormat( "yyyy-MM-dd").format(account.getUseTime())));
				pstmt.setInt(6,account.getRecorder().getId());
				pstmt.setString(7, (new SimpleDateFormat("yyyy-MM-dd").format(new Date())));
				
				int urs = pstmt.executeUpdate();
				if(urs != 0) {
					result = 1;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<Account> getAccountList(Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Account> accounts = new ArrayList<Account>();
		Account account = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select a.*,u1.*,u2.* from ztinfomgr.T_Account as a,ztinfomgr.T_User as u1,ztinfomgr.T_User as u2"+
					" where a.mgrId = u1.id and a.recorderId = u2.id and a.state = 0 order by a.id limit ?,?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				account = new Account();
				account.setId(rs.getInt("a.id"));
				account.setMgr(new User());
				account.getMgr().setId(rs.getInt("u1.id"));
				account.getMgr().setName(rs.getString("u1.name"));
				account.setMoneyQuantity(rs.getDouble("a.moneyQuantity"));
				if(rs.getInt("a.useType") == 0){
					account.setUseType("支出");
				}else {
					account.setUseType("收入");
				}
				account.setUseTime(rs.getDate("a.useTime"));
				account.setDescription(rs.getString("a.description"));
				account.setRecorder(new User());
				account.getRecorder().setId(rs.getInt("u2.id"));
				account.getRecorder().setName(rs.getString("u2.name"));
				account.setRecordTime(rs.getDate("a.recordTime"));
				
				accounts.add(account);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return accounts;	
	}

	public Account getAccount(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		Account account = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select a.*,u1.*,u2.* from ztinfomgr.T_Account as a,ztinfomgr.T_User as u1,ztinfomgr.T_User as u2"+
		" where a.mgrId = u1.id and a.recorderId = u2.id and a.id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				account = new Account();
				account.setId(rs.getInt("a.id"));
				account.setMgr(new User());
				account.getMgr().setId(rs.getInt("u1.id"));
				account.getMgr().setName(rs.getString("u1.name"));
				account.setMoneyQuantity(rs.getDouble("a.moneyQuantity"));
				if(rs.getInt("a.useType") == 0){
					account.setUseType("支出");
				}else {
					account.setUseType("收入");
				}
				account.setUseTime(rs.getDate("a.useTime"));
				account.setDescription(rs.getString("a.description"));
				account.setRecorder(new User());
				account.getRecorder().setId(rs.getInt("u2.id"));
				account.getRecorder().setName(rs.getString("u2.name"));
				account.setRecordTime(rs.getDate("a.recordTime"));
			}else {
				System.out.println("获取账目失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return account;	
	}

	public int modifyAccount(Account account) {
		
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		int result = 0;
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Account set useType = ?,useTime = ?,moneyQuantity = ?,description = ? where id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, (account.getUseType().equals("0")?0:1));
			pstmt.setString(2, (new SimpleDateFormat( "yyyy-MM-dd").format(account.getUseTime())));
			pstmt.setDouble(3, account.getMoneyQuantity());
			pstmt.setString(4, account.getDescription());
			pstmt.setInt(5, account.getId());
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;	
	}

	public List<AccountQuestion> getQuestionList(int AccountId,Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		List<AccountQuestion> questions = new ArrayList<AccountQuestion>();
		AccountQuestion question = null;
		
		String sql = "select * from T_AccountQuestion as aq,T_User as u1,T_User as u2 " +
					"where aq.accountId = ? and aq.asker = u1.id and aq.replyer = u2.id and aq.state != 2 order by aq.id desc limit ?,?";
		
		try{
			 pstmt = dbt.getPstmt(sql);
			 pstmt.setInt(1, AccountId);
			 pstmt.setInt(2, page.getStartRow());
			 pstmt.setInt(3, page.getPageSize());
			 rs = pstmt.executeQuery();
			 
			 while(rs.next()) {
				 question = new AccountQuestion();
				 question.setId(rs.getInt("aq.id"));
				 
				 question.setAccount(new Account());
				 question.getAccount().setId(rs.getInt("aq.accountId"));
				 
				 question.setAsker(new User());
				 question.getAsker().setId(rs.getInt("u1.id"));
				 question.getAsker().setName(rs.getString("u1.name"));
				 
				 question.setQuestionContent(rs.getString("QuestionContent"));
				 question.setAskTime(rs.getDate("aq.askTime"));
				 
				 question.setReplyer(new User());
				 if(!(rs.getInt("aq.asker") == rs.getInt("aq.replyer"))) {
					 question.getReplyer().setId(rs.getInt("u2.id"));
					 question.getReplyer().setName(rs.getString("u2.name"));
					 
					 question.setSolution(rs.getString("aq.solution"));
					 question.setReplyTime(rs.getDate("replyTime"));
				 }
				 question.setState(rs.getInt("aq.state"));
				 
				 questions.add(question);
			 }
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return questions;
	}

	public int newQuestion(AccountQuestion question) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "insert into T_AccountQuestion(accountId,asker,questionContent,askTime,replyer) values(?,?,?,?,?)";
		
		try{
			 pstmt = dbt.getPstmt(sql);
		System.out.println(question.getAccount().getId());
			 pstmt.setInt(1, question.getAccount().getId());
			 pstmt.setInt(2, question.getAsker().getId());
			 pstmt.setString(3, question.getQuestionContent());
			 pstmt.setString(4, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
			 pstmt.setInt(5, question.getAsker().getId());
			 
			 int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	
	}

	public int replyOrAnswerQuestion(AccountQuestion question) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "update T_AccountQuestion set replyer = ?,replyTime = ?,solution = ? where id = ?";
		
		try{
			 pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, question.getReplyer().getId());
			pstmt.setString(2, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setString(3, question.getSolution());
			pstmt.setInt(4, question.getId());
			 
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteAccountQ(AccountQuestion question) {
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "delete from T_AccountQuestion where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, question.getId());
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int addAccountMessage(Message message) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "insert into T_Message(messageType,userId,mesTime,content) values(?,?,?,?)";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, message.getMessageType());
			pstmt.setInt(2, message.getUser().getId());
			pstmt.setString(3, new SimpleDateFormat("yy-MM-dd hh:mm:ss").format(new Date()));
			pstmt.setString(4,message.getContent());
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<Message> getAccountMessages(Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Message> messages = new ArrayList<Message>();
		Message message = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select m.*,u.id,u.name from T_Message as m, T_User as u " + 
				"where m.messageType = 1 and m.userId = u.id and m.state = 0 order by m.id desc limit ?,? ";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				message = new Message();
				message.setId(rs.getInt("m.id"));
				message.setMessageType(1);
				message.setUser(new User()); 
				message.getUser().setId(rs.getInt("u.id"));
				message.getUser().setName(rs.getString("u.name"));
				message.setMesTime(rs.getTimestamp("mesTime"));
				message.setContent(rs.getString("content"));
				messages.add(message);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return messages;
	}

	public int deleteAccount(int accountId) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "update T_Account set state = 1 where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, accountId);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteMessage(int MessageId) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "update T_Message set state = 1 where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, MessageId);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}
}
